
with network_whole_massage as(
select code,
       name,
       agent_code,
       agent_name,
       virt_code,  --虚拟代理区
       virt_name
from jms_dim.dim_network_whole_massage
    where
    -- virt_name='京津冀代理区'
     network_type=6
),
     sitearrival_base as(
         select
          scan_site_code,
          max(scan_site) as scan_site,
          round(count(1)/7,4)  as day_avg
         from (
                select waybill_no,
                       scan_site_code,
                       scan_site,
                       scan_time,
                row_number() over (partition by scan_site_code,waybill_no order by scan_time desc) as rn
         from jms_dwd.dwd_tab_barscan_sitearrival_base_dt
         where dt>=date_sub('{{ execution_date |  cst_ds }}',6)
         and dt<='{{ execution_date |  cst_ds }}'
                  ) tmp
         where tmp.rn=1
         group by scan_site_code
     ),

     barscan_deliver_base as(
          select
          scan_site_code,count(1) as cnt
          from (
              select
         scan_site_code,
          send_user_code as scan_user_code
         from jms_dwd.dwd_tab_barscan_deliver_base_dt
         where dt='{{ execution_date |  cst_ds }}'
         group by send_user_code,scan_site_code
                   )tmp
         group by scan_site_code
     ),
     taking_union_sign_hour_sum as (
             select
                             network_code,
                            --  network_name,
                             sum(scan_sum) as scan_sum
                 from jms_dm.dm_network_taking_union_sign_hour_sum_dt
                 where dt='{{ execution_date |  cst_ds }}'
                 and type1='时效签收量'
                 group by network_code

     ),
     network_retention_summary as (
         select
       end_agent_code,
       max(end_agent_name) as end_agent_name,
       final_sign_network_code,
       max(final_sign_network_name) as final_sign_network_name,
       sum(retention_count_1d) as retention_count_1d,
       round(sum(retention_count_1d)/sum(retention_count_7d),4)   as retention_1d_rate,
       sum(retention_count_7d) as retention_count_7d,
       sum(retention_count_2d) -sum(retention_count_1d) as 2_1_sum, --2天滞留减一天内滞留
       round((sum(retention_count_2d) -sum(retention_count_1d))/sum(retention_count_7d) ,4)   as retention_second_day_rate,
       sum(retention_count_7d) -sum(retention_count_2d) as 7_2_sum, --2天滞留减一天内滞留
       round((sum(retention_count_7d) -sum(retention_count_2d))/sum(retention_count_7d),4)  as retention_3_7_day_rate,
       -- 以下为新增字段
               sum(retention_count_3d) as    retention_count_3d,     --三日滞留总量
               round(sum(retention_count_1d)/sum(retention_count_3d),4)   as retention_1d_new_rate,        -- 新T_1日占比
                round((sum(retention_count_2d) -sum(retention_count_1d))/sum(retention_count_3d) ,4)   as retention_second_day_new_rate,
               (sum(retention_count_3d)-sum(retention_count_2d)) as t_3_retention_sum,  --T-3滞留量
               round((sum(retention_count_3d)-sum(retention_count_2d))/sum(retention_count_3d),4) as retention_3_2_day_rate   --第三天滞留率

from jms_dm.dm_network_retention_summary_dt
where dt='{{ execution_date |  cst_ds }}'
and is_contain_difficult=2
group by final_sign_network_code,end_agent_code
     )
insert overwrite table jms_tmp.dm_network_outbreak_dt partition (dt)
-- 网点防爆舱明细临时表
select
       a.virt_code    --虚拟代理区code
       ,a.virt_name  --虚拟代理区
       ,a.agent_code --代理区code
       ,a.agent_name --代理区名字
       ,a.code     --网点code
       ,a.name     --网点
       ,b.day_avg   --日均到件量
       ,c.scan_sum  -- 当日签收量
       ,d.cnt      -- 当日派件员数量
       ,e.retention_count_7d  --七天内滞留量
       ,e.retention_count_7d/c.scan_sum as  digestion_ability  --消化能力
       ,e.retention_count_1d   -- 一天内滞留量
       ,retention_1d_rate    -- 一天滞留率
       ,e.2_1_sum           -- 第二天滞留量
       ,e.retention_second_day_rate   -- 第二天滞留量占七天滞留总量的比例
       ,e.7_2_sum                 -- 3-7天滞留量
       ,e.retention_3_7_day_rate-- 第3天到第7天滞留量占七天滞留总量的比例
        -- 以下为新增字段
        ,e.retention_count_3d             --三天滞留总量
        ,e.retention_count_3d/c.scan_sum as  new_digestion_ability   --新的压力值
        ,e.retention_1d_new_rate           -- T-1新的滞留率
        ,e.retention_second_day_new_rate   --第二天新的滞留率
        ,e.t_3_retention_sum   --T-3滞留量
        ,e.retention_3_2_day_rate  --T-3的滞留率
       ,'{{ execution_date |  cst_ds }}' as dt
from network_whole_massage a
left join sitearrival_base b
on a.code=b.scan_site_code
left join taking_union_sign_hour_sum c
on a.code=c.network_code
left join barscan_deliver_base d
on a.code=d.scan_site_code
left join network_retention_summary e
on a.code =e.final_sign_network_code
distribute by 10
;
msck repair table jms_tmp.dm_network_outbreak_dt;
-- 防爆舱明细
with network_outbreak_day as(
    select
    virt_code
,virt_name
,agent_code
,agent_name
,code
,name
,day_avg
,scan_sum
,cnt
,retention_count_7d
, digestion_ability
,retention_count_1d
,retention_1d_rate
,2_1_sum
,retention_second_day_rate
,7_2_sum
,retention_3_7_day_rate
,dt
    from jms_tmp.dm_network_outbreak_dt
    where dt='{{ execution_date |  cst_ds }}'

),
     network_outbreak_last_day  as(
         select
 virt_code
,virt_name
,agent_code
,agent_name
,code
,name
,day_avg
,scan_sum
,cnt
    from jms_tmp.dm_network_outbreak_dt
    where dt=date_add('{{ execution_date |  cst_ds }}',-1)
     ),
 tmp_day_last_day as (
     select
     a.virt_code
    ,a.virt_name
    ,a.agent_code
    ,a.agent_name
    ,a.code
    ,a.name
    ,a.day_avg as day_arrive_sum
    ,b.day_avg as last_day_arrive_sum
    , round((a.day_avg-b.day_avg)/b.day_avg ,4) as arrive_rate  --到件环比
    ,a.scan_sum  as day_sign_sum
    ,b.scan_sum as last_day_sign_sum
    ,round((a.scan_sum-b.scan_sum)/b.scan_sum ,4) as sign_rate      --签收比
    ,nvl(a.cnt,0) as day_deliver_sum
    ,nvl(b.cnt,0) as last_day_deliver_sum
     ,case when nvl(a.cnt,0)=0 and nvl(b.cnt,0)=0 then 0
                 when nvl(a.cnt,0)!=0 and nvl(b.cnt,0)=0 then 1.00
                 when nvl(a.cnt,0)=0 and nvl(b.cnt,0)!=0 then -1.00
                 else
           round((nvl(a.cnt,0)-nvl(b.cnt,0))/nvl(b.cnt,0) ,4) end as deliver_rate  --派件员环比
     ,retention_count_7d
     ,digestion_ability
     ,retention_count_1d
     ,retention_1d_rate
     ,2_1_sum
     ,retention_second_day_rate
     ,7_2_sum
     ,retention_3_7_day_rate
     ,a.dt
     from network_outbreak_day a
     left join network_outbreak_last_day b
     on a.code=b.code
 ),
     mng_explosion_warehouse_network as(
select
        final_sign_network_code,
       final_sign_network_name,
              end_agent_code,
              end_agent_name,
              end_provider_id,
              end_provider_name
from jms_dm.dm_mng_explosion_warehouse_network_punish_dt
where dt='{{ execution_date |  cst_ds }}'
)
insert overwrite table jms_dm.dm_network_outbreak_detail_dt partition (dt)
-- 网点爆仓明细正式表
     select
     virt_code  --虚拟代理区code
    ,virt_name   --虚拟代理区
    ,a.end_agent_code as agent_code  --代理区code
    ,a.end_agent_name as agent_name   --代理区
    ,code    --网点code
    ,name    --网点
    ,day_arrive_sum  --当日到达量
    ,last_day_arrive_sum   --昨日到大量
    ,arrive_rate  --到件环比
    ,day_sign_sum   --当日签收量
    ,last_day_sign_sum  --昨日签收量
    ,sign_rate      --签收比
    ,day_deliver_sum  --今日派件员数量
    ,last_day_deliver_sum  --昨日派件员数量
    ,deliver_rate  --派件员环比
    ,retention_count_7d   --七天滞留量
    ,digestion_ability    --消化能力
    ,retention_count_1d   --当日滞留量
    ,retention_1d_rate    --当日滞留率
    ,2_1_sum   --第二日滞留量
    ,retention_second_day_rate  -- 第二日滞留量占比
    ,7_2_sum   --t_2到T-7滞留量
    ,retention_3_7_day_rate  -- t_2到T-7滞留量占比
    , b.dt as date_time   --业务日期
    ,a.end_provider_id
    ,a.end_provider_name
    , b.dt
from mng_explosion_warehouse_network a
join tmp_day_last_day b
on a.final_sign_network_code=b.code
distribute by 10
;
-- 网点滞留件预警明细
with network_outbreak_day as(
    select virt_code
         , virt_name
         , agent_code
         , agent_name
         , code
         , name
         , day_avg
         , scan_sum
         , cnt
         , retention_count_7d
         , digestion_ability
         , retention_count_1d
         , retention_1d_rate
         , 2_1_sum
         , retention_second_day_rate
         , 7_2_sum
         , retention_3_7_day_rate
         , retention_count_3d            --三天滞留总量
         , new_digestion_ability         --新的压力值
         , retention_1d_new_rate         -- T-1新的滞留率
         , retention_second_day_new_rate --第二天新的滞留率
         , t_3_retention_sum             --T-3滞留量
         , retention_3_2_day_rate        --T-3的滞留率
         , dt
    from jms_tmp.dm_network_outbreak_dt
    where dt = '{{ execution_date |  cst_ds }}'
),
     network_outbreak_last_day  as(
         select virt_code
              , virt_name
              , agent_code
              , agent_name
              , code
              , name
              , day_avg
              , scan_sum
              , cnt
         from jms_tmp.dm_network_outbreak_dt
         where dt = date_add('{{ execution_date |  cst_ds }}', -1)
     ),
 tmp_day_last_day as (
     select
     a.virt_code
    ,a.virt_name
    ,a.agent_code
    ,a.agent_name
    ,a.code
    ,a.name
    ,a.day_avg as day_arrive_sum
    ,b.day_avg as last_day_arrive_sum
    , round((a.day_avg-b.day_avg)/b.day_avg ,4) as arrive_rate  --到件环比
    ,a.scan_sum  as day_sign_sum
    ,b.scan_sum as last_day_sign_sum
    ,round((a.scan_sum-b.scan_sum)/b.scan_sum ,4) as sign_rate      --签收比
    ,nvl(a.cnt,0) as day_deliver_sum
    ,nvl(b.cnt,0) as last_day_deliver_sum
     ,case when nvl(a.cnt,0)=0 and nvl(b.cnt,0)=0 then 0
           when nvl(a.cnt,0)!=0 and nvl(b.cnt,0)=0 then 1.00
            when nvl(a.cnt,0)=0 and nvl(b.cnt,0)!=0 then -1.00
           else
     round((nvl(a.cnt,0)-nvl(b.cnt,0))/nvl(b.cnt,0) ,4) end as deliver_rate  --派件员环比
     ,retention_count_7d
     ,digestion_ability
     ,retention_count_1d
     ,retention_1d_rate
     ,2_1_sum
     ,retention_second_day_rate
     ,7_2_sum
     ,retention_3_7_day_rate
          -- 以下为新增
          ,a.retention_count_3d            --三天滞留总量
         , a.new_digestion_ability         --新的压力值
         , a.retention_1d_new_rate         -- T-1新的滞留率
         , a.retention_second_day_new_rate --第二天新的滞留率
         , a.t_3_retention_sum             --T-3滞留量
         , a.retention_3_2_day_rate        --T-3的滞留率
     ,a.dt
     from network_outbreak_day a
     left join network_outbreak_last_day b
     on a.code=b.code
 ),
     explosion_warehouse_network_punish as(
select
        final_sign_network_code,
       final_sign_network_name,
              end_agent_code,
              end_agent_name,
              end_provider_id,
              end_provider_name
from jms_dm.dm_mng_explosion_warehouse_network_second_dt
where dt='{{ execution_date |  cst_ds }}'
)
 insert overwrite table jms_dm.dm_network_retention_warming_detail_new_dt partition (dt)
-- 网点滞留预警明细正式表新20230307
     select
     virt_code  --虚拟代理区code
    ,virt_name   --虚拟代理区
    ,a.end_agent_code as agent_code  --代理区code
    ,a.end_agent_name as agent_name   --代理区
    ,a.end_provider_id
    ,a.end_provider_name
    ,code    --网点code
    ,name    --网点
    ,day_arrive_sum  --当日到达量
    ,last_day_arrive_sum   --昨日到大量
    ,arrive_rate  --到件环比
    ,day_sign_sum   --当日签收量
    ,last_day_sign_sum  --昨日签收量
    ,sign_rate      --签收比
    ,day_deliver_sum  --今日派件员数量
    ,last_day_deliver_sum  --昨日派件员数量
    ,deliver_rate  --派件员环比
           -- 以下为新增
         , b.retention_count_3d            --三天滞留总量
         , b.new_digestion_ability         --新的压力值
          ,retention_count_1d   --当日滞留量
         , b.retention_1d_new_rate         -- T-1新的滞留率
          ,b.2_1_sum   --第二日滞留量
         , b.retention_second_day_new_rate --第二天新的滞留率
         , b.t_3_retention_sum             --T-3滞留量
         , b.retention_3_2_day_rate        --T-3的滞留率
          , b.dt as date_time   --业务日期
          , b.dt
from explosion_warehouse_network_punish a
join tmp_day_last_day b
on a.final_sign_network_code=b.code
distribute by 10
;